﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_IDAL;
using HCQ2_Model.VR;
using HCQ2_Model.VR.Params;
using System.Globalization;
using HCQ2_Common.SQL;
using HCQ2_Common;
using HCQ2_Model.VR.Model;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRGoldLogDAL: IT_VRGoldLogDAL
    {
        /// <summary>
        ///  获取金币日志列表
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回记录总数</param>
        /// <returns></returns>
        public List<T_VRGoldLogModel> GetGoldList(GoldParam param, out int total)
        {
            sb?.Clear();
            sb.Append($" SELECT TOP {param.rows} * FROM(SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY gold.create_date desc) AS number,* FROM (SELECT users.login_name,logs.* FROM ");
            string sqlParam = GetSQL(param);
            sb.Append(sqlParam);
            sb.Append($" )gold)a WHERE a.number>{(param.page - 1) * param.rows};");
            //记录数
            string totalPram = $"SELECT COUNT(0) AS num FROM {sqlParam} ";
            total = Helper.ToInt(SqlHelper.ExecuteScalar(totalPram));
            return SqlHelper.ExecuteList<T_VRGoldLogModel>(sb.ToString());
        }
        private string GetSQL(GoldParam param)
        {
            StringBuilder d = new StringBuilder();
            d.Append($" (SELECT log_id,user_id,user_name,unit_code,dbo.fn_selectdict(unit_code)AS unit_name,gold_num,gold_type,operator_type,create_id,create_name,CONVERT(varchar(100),create_date,120) AS create_date,BeforBuyGold,BeforGiveGold,BuyGold,GiveGold,Content,SubtractBuyGold,SubtractGiveGold FROM T_VRGoldLog WHERE 1=1 ");
            //单位代码
            if (!string.IsNullOrEmpty(param.unitCode))
                d.Append($" AND unit_code='{param.unitCode}' ");
            //金币类型
            if (param.goldType > 0)
                d.Append($" AND gold_type={param.goldType} ");
            //操作类型
            if (param.operatorType > 0)
                d.Append($" AND operator_type={param.operatorType} ");
            //日期
            if (!string.IsNullOrEmpty(param.inDate))
                param.inDate = DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(param.outDate))
                param.outDate = DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                d.AppendFormat($" AND create_date>='{param.inDate}' AND create_date<='{param.outDate}' ");
            else if (!string.IsNullOrEmpty(param.inDate) && string.IsNullOrEmpty(param.outDate))
                d.AppendFormat($" AND create_date>='{param.inDate}' ");
            else if (string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                d.AppendFormat($" AND create_date<='{param.outDate}' ");
            d.Append(")logs INNER JOIN(SELECT user_id, login_name FROM dbo.T_User WHERE 1 = 1 ");
            //登录名、用户名
            if (!string.IsNullOrEmpty(param.userName))
                d.Append($" AND (login_name LIKE '%{param.userName}%' OR user_name LIKE '%{param.userName}%' ) ");
            d.Append(" )users ON logs.user_id=users.user_id ");
            return d.ToString();
        }

        /// <summary>
        ///  获取销售明细
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回记录总数</param>
        /// <returns></returns>
        public List<T_VRSellGoldLogModel> GetSellGoldList(SellGoldParam param, out int total)
        {
            sb?.Clear();
            sb.Append($" SELECT TOP {param.rows} * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY r.create_date DESC) AS num FROM  ");
            string sqlParam = GetSQL(param);
            sb.Append(sqlParam);
            sb.Append($")o WHERE o.num>{(param.page - 1) * param.rows}");
            //记录数
            string totalPram = $"SELECT COUNT(0) AS num FROM {sqlParam} ";
            total = SqlHelper.ExecuteScalarInt32(totalPram); 
            return SqlHelper.ExecuteList<T_VRSellGoldLogModel>(sb.ToString());
        }
        /// <summary>
        ///  获取销售明细查询条件组合
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        private string GetSQL(SellGoldParam param)
        {
            StringBuilder d = new StringBuilder();
            d.Append($@"(SELECT logs.*,orders.*,f.folder_name FROM 
                (SELECT log_id, create_name, create_id, user_name, user_id, gold_num, operator_type, ParentOrderCode, TotalMoney,
                 CONVERT(varchar(100), create_date, 120) AS create_date FROM dbo.T_VRGoldLog WHERE FolderId>0 AND ISNULL(ParentOrderCode, '') <> '')logs INNER JOIN
                 (SELECT OrderCode, file_name,file_id, file_code FROM dbo.N_OrderCommodityInfo)orders ON logs.ParentOrderCode = orders.OrderCode INNER JOIN
                 (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation)rel ON orders.file_id=rel.file_id INNER JOIN
                 (SELECT folder_id,folder_name FROM dbo.T_DocumentFolder)f ON rel.folder_id=f.folder_id UNION SELECT log_id,create_name,create_id,user_name,user_id,gold_num,operator_type,ParentOrderCode,TotalMoney,CONVERT(varchar(100),create_date,120) AS create_date,OrderCode='',file_name='',file_id=0,file_code='',folder_name='' FROM dbo.T_VRGoldLog WHERE FolderId>0 AND gold_type=1 AND ISNULL(ParentOrderCode,'')='' )r WHERE 1=1");
            //商品名
            if(!string.IsNullOrEmpty(param.fileCode))
                d.Append($" AND r.file_name='{param.fileCode}' ");
            //销售人
            if (!string.IsNullOrEmpty(param.sellName))
                d.Append($" AND r.create_name='{param.sellName}' ");
            //购买人
            if (!string.IsNullOrEmpty(param.payName))
                d.Append($" AND r.user_name='{param.payName}' ");
            //订单号
            if (!string.IsNullOrEmpty(param.orderNumber))
                d.Append($" AND r.ParentOrderCode='{param.orderNumber}' ");
            //操作类别
            if (param.operatorType > 0)
                d.Append($" AND r.operator_type={param.operatorType } ");
            //日期
            if (!string.IsNullOrEmpty(param.inDate))
                param.inDate = DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(param.outDate))
                param.outDate = DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                d.AppendFormat($" AND r.create_date>='{param.inDate}' AND r.create_date<='{param.outDate}' ");
            else if (!string.IsNullOrEmpty(param.inDate) && string.IsNullOrEmpty(param.outDate))
                d.AppendFormat($" AND r.create_date>='{param.inDate}' ");
            else if (string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                d.AppendFormat($" AND r.create_date<='{param.outDate}' ");
            return d.ToString();
        }
        /// <summary>
        ///  获取销售额列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<SaleMoneyResult> GetSaleList(SaleMoneyParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT logs.*,orders.file_id,orders.file_code,ISNULL(orders.file_name,'充值') AS file_name FROM
            (SELECT * FROM (SELECT g.operator_type,user_id,user_name,login_name=(SELECT login_name FROM dbo.T_User u WHERE u.user_id=g.user_id),
            user_phone=(SELECT u.user_phone FROM dbo.T_User u WHERE u.user_id=g.user_id),create_id,create_name,
            sale_name=(SELECT login_name FROM dbo.T_User u WHERE u.user_id=g.create_id),
            sale_phone=(SELECT u.user_phone FROM dbo.T_User u WHERE u.user_id=g.create_id),ParentOrderCode,CONVERT(varchar(100),create_date,120) AS create_date,gold_num FROM dbo.T_VRGoldLog g
            WHERE g.FolderId>0 ");
            //销售人
            if (param.user_id > 0)
                sb.Append($" AND g.create_id={param.user_id}");
            //商铺
            if (param.folder_id > 0)
                sb.Append($" AND g.FolderId={param.folder_id}");
            //状态
            if (param.sale_type > 0)
                sb.Append($" AND g.operator_type={param.sale_type} ");
            sb.Append(")l  WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND l.user_name LIKE '%{param.keyword}%' OR l.user_phone LIKE '%{param.keyword}%' ");         
            sb.Append($@")logs LEFT JOIN
             (SELECT OrderCode, file_name,file_id, file_code FROM dbo.N_OrderCommodityInfo)orders ON logs.ParentOrderCode = orders.OrderCode ORDER BY logs.create_date DESC;");
            return SqlHelper.ExecuteList<SaleMoneyResult>(sb.ToString());
        }
        /// <summary>
        ///  获取合伙人申请
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<FounderList> GetFounderList(FounderParam param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {param.rows} * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY a.apply_date {(param.sortType > 0?"ASC":"DESC")} )num FROM 
            (SELECT id,user_id AS apply_id,folder_id,CONVERT(varchar(100),apply_date,120) AS apply_date,apply_name AS user_name,apply_phone AS user_phone,folder_name,apply_status,approve_name,CONVERT(varchar(100),approve_date,120) AS approve_date,
            approve_note,ref_id,ref_name,ref_userid=(SELECT user_guid FROM dbo.T_User WHERE user_id=y.ref_id),
            du_friend=(SELECT COUNT(0) FROM dbo.DD_1 with(nolock) WHERE (dd_id_1={user_id} AND dd_id_2=y.user_id) OR 
            (dd_id_1=y.user_id AND dd_id_2={user_id})),
            du_friend2=(SELECT COUNT(0) FROM dbo.DD_2 with(nolock) WHERE (dd_id_1={user_id} AND dd_id_2=y.user_id) OR 
            (dd_id_1=y.user_id AND dd_id_2={user_id})),du_friend3=(SELECT COUNT(0) FROM dbo.DD_3 with(nolock) WHERE 
            (dd_id_1={user_id} AND dd_id_2=y.user_id) OR (dd_id_1=y.user_id AND dd_id_2={user_id}))
            FROM T_VRFounderApply y WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (apply_name LIKE '%{param.keyword}%' OR apply_phone LIKE '%{param.keyword}%') ");
            if (param.folder_id > 0)
                sb.Append($" AND folder_id={param.folder_id } ");
            else
                sb.Append($@" AND folder_id IN(SELECT folder_id FROM dbo.T_DocumentFolder WHERE founder_id={user_id}) ");
            if (param.apply_status > -1)
                sb.Append($" AND apply_status={param.apply_status} ");
            sb.Append($@")a INNER JOIN 
            (SELECT du_id,city,user_type,CONVERT(varchar(100),gps_time,120) AS LastLocationTime,gps_x,gps_y,
            sense=dbo.GetDistance({param.gps_x},{param.gps_y},gps_x,gps_y) FROM dbo.DD_User)d ON d.du_id=a.apply_id INNER JOIN 
            (SELECT user_id,user_guid as userid,user_status as dd_line,user_img,user_note FROM dbo.T_User)u ON u.user_id = a.apply_id 
            )l WHERE l.num>{(param.page-1)*param.rows}");
            return SqlHelper.ExecuteList<FounderList>(sb.ToString());
        }
    }
}